import mysql from 'mysql2/promise';
import config from './configuration.js';
import logger from './logger.js';

let pool = null;

/**
 * 百程创科采用mysql内置分区表功能,并不做实际的表区分
用户信息表   按opnid分为200个分区
用户已购待使用券信息表 按opnid分为200个分区
用户已购待使用券信息表 按opnid分为200个分区
用户券使用记录  按日期进行分区  每周一个分区  保留一年  每周一创建下周的分区并删除一年以上的对应的分区
订单信息表     按日期进行分区  每周一个分区  保留一年  每周一创建下周的分区并删除一年以上的对应的分区
*/

async function getPool() {
    if (pool) {
        return pool;
    }
    try {
        pool = mysql.createPool(config.dbConfig);
        await pool.query('SELECT 1 + 1 ');
        logger.info('mysql 链接成功');
    } catch (error) {
        logger.error('createPool error: ', error.message, 'dbConfig: ', JSON.stringify(config.dbConfig));
        return error;
    }

    return pool;
}

async function query(sql, params) {
    const pool = await getPool();
    if (!pool) {
        return null;
    }
    try {
        const [rows] = await pool.query(sql, params);
        return rows;
    } catch (error) {
        logger.error(error);
        return error;
    }
}

//检查是否存在重复的记录
async function checkExist(table, conditions) {
    const pool = await getPool();
    if (!pool) {
        return null;
    }
    let where = ' WHERE 1=1 ';
    for (let key in conditions) {
        if (conditions[key] != undefined) {
            where += ` and ${key}='${conditions[key]}' `;
        }
    }
    console.log('where', where);
    try {
        const [rows] = await pool.query(`SELECT COUNT(*) as count FROM ${table}  ${where}`);
        return rows[0].count > 0;
    } catch (error) {
        logger.error('checkExist error: ', error.message, table, JSON.stringify(conditions));
        return false;
    }
}

async function findOne(table, conditions) {
    const pool = await getPool();
    if (!pool) {
        return null;
    }
    let { where, params, fields } = whereString(conditions);

    // let sql = `SELECT ${fields} FROM ${table}  ${where}`;
    try {
        const [rows] = await pool.query(`SELECT * FROM ${table}  ${where}  LIMIT 1`, params);
        return rows[0];
    } catch (error) {
        logger.error('findOne error: ', error.message, table, JSON.stringify(conditions));
        return error;
    }
}

//

/**
 *
 * @param {*} table string
 * @param {*} conditions 条件 {}
 * @param {*} orderBy string
 * @param {*} direction strirng
 * @param {*} limit
 * @param {*} offset
 * @param {*}areas field 范围查询字段字段 start 开始 end 结束
 * @param {*}likestr column 模糊查询字段 str 模糊查询值
 * @param {*}leftjoin jointable 链接的表名 origintable 被链接的表名 originfield 被链接的字段  joinfield 链接的字段 joinsql 连接语句
 * @returns
 */
async function findAll(table, conditions, orderBy = null, direction = ' DESC', limit = null, offset = null, areas, likestr, leftjoin, inarr, dispColumn = '*',customizeWhere='') {
    const pool = await getPool();
    if (!pool) {
        logger.error('findAll 获取连接失败');
        return null;
    }
    let count = 0;
    let needCount = false;
    if (limit != null && offset != null) {
        needCount = true;
    }
    //检查table是否为数组
    if (Array.isArray(table)) {
        let sqls = [];
        let field;
        table.forEach((tableName) => {
            let { where, params, fields } = whereString(conditions, areas, likestr, leftjoin, inarr, tableName);
            sqls.push(`SELECT * FROM ${tableName} ${where}`);
            field = fields;
        });
        let sql = sqls.join(' UNION ALL ');
        sql = `SELECT COUNT(*) as count FROM (${sql}) as combined_orders`;
        if (needCount) {
            try {
                const [rows] = await pool.query(sql);
                count = rows[0].count;
                if (limit && count == 0) {
                    return { count, rows: [] };
                }
            } catch (error) {
                logger.error('findAll error: ', error.message, table, JSON.stringify(conditions));
                return error;
            }
        }

        sql = sqls.join(' UNION ALL ');
        if (fields == '*') {
            fields = dispColumn;
        }
        sql = `SELECT ${field} FROM (${sql}) as combined_orders`;
        if (orderBy) {
            sql += ` ORDER BY ${orderBy} ${direction}`;
        }
        if (limit) {
            sql += ` LIMIT ${offset * limit},${limit}`;
        }
        try {
            // logger.info(sql, JSON.stringify(params));
            const [rows] = await pool.query(sql);
            return needCount
                ? {
                      count,
                      rows,
                  }
                : rows;
        } catch (error) {
            logger.error('findAll error: ', error.message, table, JSON.stringify(conditions));
            return null;
        }
    } else {
        let { where, params, fields } = whereString(conditions, areas, likestr, leftjoin, inarr, table,customizeWhere);
        if (needCount) {
            const [rows] = await pool.query(`SELECT COUNT(*) as count FROM ${table} ${where}`);
            count = rows[0].count;
            if (limit && count == 0) {
                return { count, rows: [] };
            }
        }
        if (fields == ' * ') {
            fields = dispColumn;
        }
        let sql = `SELECT ${fields} FROM ${table}  ${where}`;
        if (orderBy) {
            sql += ` ORDER BY ${orderBy} ${direction}`;
        }
        if (limit) {
            sql += ` LIMIT ${offset * limit},${limit}`;
        }
        try {
             logger.info(sql, JSON.stringify(params));
            const [rows] = await pool.query(sql, params);
            return needCount
                ? {
                      count,
                      rows,
                  }
                : rows;
        } catch (error) {
            logger.error('findAll error: ', error.message, table, JSON.stringify(conditions));
            return null;
        }
    }
}

async function findAllNoPage(table, conditions, orderBy = null, direction = ' DESC', limit = null, offset = null, areas, likestr, leftjoin, inarr) {
    const pool = await getPool();
    if (!pool) {
        logger.error('findAllNoPage 获取连接失败');
        return null;
    }
    let { where, params, fields } = whereString(conditions, areas, likestr, leftjoin, inarr, table);

    let sql = `SELECT ${fields} FROM ${table}  ${where}`;
    if (orderBy) {
        sql += ` ORDER BY ${orderBy} ${direction}`;
    }
    if (limit) {
        sql += ` LIMIT ${offset * limit},${limit}`;
    }
    logger.info('findall', sql);
    try {
        const [rows] = await pool.query(sql, params);
        return rows;
    } catch (error) {
        logger.error('findAllNoPage error: ', error.message, table, JSON.stringify(conditions));
        return null;
    }
}

//根据对象生成where语句
function whereString(conditions, areas = [], likestr = [], leftjoin = [], inarr = [], table,customizeWhere) {
    let where = '';
    let fields = ' ';
    leftjoin.forEach((item) => {
        where += ` LEFT JOIN ${item.table} ON ${item.on} `;
        if (item.fields != '' && item.fields != null) {
            fields += item.fields + ',';
        }
    });

    fields = fields.substring(0, fields.length - 1); //去掉多余的逗号
    if (fields == '') {
        fields = ' * ';
    }
    where += ' WHERE 1=1 and ';
    let params = [];

    if (areas != null) {
        areas.forEach((area) => {
            where += ` ${area.field} between '${area.start}' AND '${area.end}' and `;
        });
    }

    if (likestr != null) {
        likestr.forEach((like) => {
            where += `${like.colum} like '${like.str}%' and `;
        });
    }
    if (inarr != null) {
        inarr.forEach((item) => {
            where += `${item.colum} in (${item.arr}) and `;
        });
    }

    for (let key in conditions) {
        if (conditions[key] != undefined) {
            if (leftjoin.length > 0) {
                where += `${table}.${key}='${conditions[key]}' AND `;
            } else {
                where += `${key}='${conditions[key]}' AND `;
            }
        }
    }
    if (where === ' WHERE ') {
        where = '';
    } else {
        if(customizeWhere!='' && customizeWhere!=null){
            where += customizeWhere;
        }else{
            where = where.substring(0, where.length - 5); // Remove the last ' AND '
        }
    }

    if (where == ' WHERE 1=1 and' && !leftjoin) {
        where = '';
    }
    return { where, params, fields };
}
async function update(table, data, conditions) {
    const pool = await getPool();
    if (!pool) {
        return null;
    }
    let where = ' WHERE 1=1 ';
    for (let key in conditions) {
        if (conditions[key] != undefined) {
            where += ` and ${key}='${conditions[key]}' `;
        }
    }
    try {
        const [result] = await pool.query(`UPDATE ${table} SET ? ${where} `, [data]);
        return result;
    } catch (error) {
        logger.error('update error: ', error.message, table, JSON.stringify(data), JSON.stringify(conditions));
        return error;
    }
}
/**
 *
 * @param {*} table
 * @param {*} conditions
 * @returns
 */
async function deleteRow(table, conditions) {
    const pool = await getPool();
    if (!pool) {
        return null;
    }
    let where = ' WHERE 1=1 ';
    for (let key in conditions) {
        if (conditions[key] != undefined) {
            where += ` and ${key}='${conditions[key]}' `;
        }
    }
    try {
        const [result] = await pool.query(`DELETE FROM ${table} ${where} `);
        return result;
    } catch (error) {
        logger.error('delete error: ', error.message, table, JSON.stringify(conditions));
        return error;
    }
}

async function close() {
    if (pool) {
        await pool.end();
    }
}

async function startTransaction() {
    const pool = await getPool();
    if (!pool) {
        return null;
    }
    const conn = await pool.getConnection();
    try {
        await conn.beginTransaction();
                return conn;
    } catch (error) {
        logger.error('startTransaction error: ', error.message);
        conn.release();
        return error;
    } 
}
async function doTransaction(conn,sqls) {
    try {
        for (let i in sqls) {
            await conn.query(sqls[i].sql, sqls[i].param);
        }
        await conn.commit();
        conn.release();
        return 1;
    } catch (error) {
        await conn.rollback();
        logger.error('do transaction error: ', error.message, JSON.stringify(sqls));
        conn.release();
        return error;
    } 
}
async function rollback(conn) {
    await conn.rollback();
    logger.error('代码执行出错 主动执行回滚 ');
    conn.release();
}


//执行事务
async function transaction(sqls) {
    const pool = await getPool();
    if (!pool) {
        return null;
    }
    let err=null;
    const conn = await pool.getConnection();
    try {
        await conn.beginTransaction();
        for (let i in sqls) {
            await conn.query(sqls[i].sql, sqls[i].param);
        }
        await conn.commit();
        // logger.info(result);
        conn.release();
        return 1;
    } catch (error) {
        await conn.rollback();
        logger.error('transaction error: ', error.message, JSON.stringify(sqls));
        err=error;
    } finally {
        conn.release();
        if(err){
            return err;
        }
        return 0;
    }
}

async function excute(sql, params) {
    const pool = await getPool();
    if (!pool) {
        return null;
    }
    try {
        const [rows] = await pool.query(sql, params);
        return rows;
    } catch (e) {
        return e;
    }
}

async function create(table, data) {
    const pool = await getPool();
    if (!pool) {
        return null;
    }
    try {
        const [result] = await pool.query(`INSERT INTO ${table} SET ?`, data);
        return result;
    } catch (error) {
        logger.error('create error: ', error.message, table, JSON.stringify(data));
        return error;
    }
}
export default {
    getPool,
    query,
    findOne,
    update,
    deleteRow,
    findAll,
    close,
    transaction,
    excute,
    create,
    checkExist,
    findAllNoPage,
    startTransaction,
    doTransaction,
    rollback
};
